﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
//using Microsoft.SqlServer.Management.Smo;
//using Microsoft.SqlServer.Management.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Diagnostics;
using ERPStudio;
using System.Text.RegularExpressions;

namespace Administrare
{
    public partial class QueryFrm : FrmRezumat
    {
        //private Server server = null;
        ToolStripComboBox tCB = null;
        SqlConnection sqlConnection = new SqlConnection();
        public QueryFrm()
            : base(null)
        {
            InitializeComponent();
        }

        private void QueryFrm_Load(object sender, EventArgs e)
        {
            tCB = AddComboBox("ConexiuniCB", Click_Conexiuni, null, ToolStripItemAlignment.Left,200);
            //tCB.Enabled = false;
            System.Configuration.Configuration config =
                           ConfigurationManager.OpenExeConfiguration(
                           ConfigurationUserLevel.None);

            KeyValueConfigurationCollection configSettings = config.AppSettings.Settings;
            if (configSettings["DefaultConnection"] == null)
            {
                config.AppSettings.Settings.Add(new KeyValueConfigurationElement("DefaultConnection", "SallyConnectionString"));
                config.Save();
            }
            ///incarca combo cu numele conexiunilor
            ///
            foreach (ConnectionStringSettings cs1 in config.ConnectionStrings.ConnectionStrings)
                tCB.Items.Add(cs1.Name);

            if (configSettings["DefaultConnection"] != null)
                tCB.Text = configSettings["DefaultConnection"].Value.ToString();
            else
            {
                Cursor.Current = Cursors.Default;
                MessageBox.Show("Nu s-a definit Default Connection", "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }

            InitServer();
            
            AddButton("Executa", global::ERPStudio.Properties.Resources.refresh_48, execute_Click, ToolStripItemAlignment.Right,true);
            
        }

        private void InitServer()
        {
            System.Configuration.Configuration config =
            ConfigurationManager.OpenExeConfiguration(
            ConfigurationUserLevel.None);

            ConnectionStringSettings cs = config.ConnectionStrings.ConnectionStrings[tCB.Text];

            // To Connect to our SQL Server - we Can use the Connection from the System.Data.SqlClient Namespace.
            
            try
            {
                if(sqlConnection!=null && sqlConnection.State== ConnectionState.Open)
                    sqlConnection.Close();
                sqlConnection.ConnectionString = cs.ConnectionString;
                sqlConnection.Open();
            }
            catch (Exception ex)
            {
                sqlConnection.Dispose();
                MessageBox.Show("Nu este posibila conectarea la serverul SQL!! \n" + ex.ToString());
                return;
            }
            sqlConnection.InfoMessage += ConnectionContext_InfoMessage;
            //build a "serverConnection" with the information of the "sqlConnection"
            //Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
            ////The "serverConnection is used in the ctor of the Server.
            //server = new Server(serverConnection);
            //server.ConnectionContext.InfoMessage += new SqlInfoMessageEventHandler(ConnectionContext_InfoMessage);
            //server.ConnectionContext.ServerMessage += new ServerMessageEventHandler(ConnectionContext_ServerMessage);
            //server.ConnectionContext.StatementExecuted += new StatementEventHandler(ConnectionContext_StatementExecuted);
        }

        //void ConnectionContext_StatementExecuted(object sender, StatementEventArgs e)
        //{
        //    MesajeTB.Text += e.ToString() + "\n";
        //}


        //void ConnectionContext_ServerMessage(object sender, ServerMessageEventArgs e)
        //{
        //    MesajeTB.Text += e.ToString() + "\n";
        //}

        void ConnectionContext_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            MesajeTB.Text += e.ToString() + "\n";
        }

        private void Click_Conexiuni(object sender, EventArgs e)
        {
            InitServer();
        }

        private void execute_Click(object sender, EventArgs e)
        {
            MesajeTB.Text = String.Empty;
            TextRTB.Text = TextRTB.Text.ToLower();
            MatchCollection matches = Regex.Matches(TextRTB.Text, @"(?<comanda>[\s|\w\W\S]+[go]+)");
            SqlCommand cmd = new SqlCommand(String.Empty,sqlConnection);
            ///adauga mai multe meniuri despartite prin punct si virgula
            foreach (Match m1 in matches)
            {
                if (m1.Success == true)
                {
                    String cmdText = m1.Groups["comanda"].ToString().Replace("go", String.Empty);

                    Boolean areSelect = cmdText.Contains("select");
                    Boolean areInsert = cmdText.Contains("insert");
                    Boolean areUpdate = cmdText.Contains("update");
                    Boolean areDelete = cmdText.Contains("delete");
                    Boolean areExec = cmdText.Contains("exec");

                    try
                    {
                        if (areInsert || areUpdate || areDelete)
                        {
                            cmd.CommandText = cmdText;
                            cmd.ExecuteNonQuery();
                        }

                        if (!areInsert && !areUpdate && !areDelete && areSelect)
                        {
                            SqlDataAdapter da = new SqlDataAdapter(cmdText, sqlConnection);
                            DataTable ds = new DataTable();
                            da.Fill(ds);

                            if (ds.Rows.Count > 0)
                            {
                                tabControl1.TabPages.Add(ds.TableName);
                                int index = tabControl1.TabPages.Count - 1;
                                TabPage tp = tabControl1.TabPages[index];
                                DataGridView dgv = new DataGridView();
                                tp.Controls.Add(dgv);
                                dgv.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader;
                                dgv.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
                                dgv.Dock = System.Windows.Forms.DockStyle.Fill;
                                dgv.Location = new System.Drawing.Point(3, 3);
                                dgv.Name = ds.TableName + "DGV";
                                dgv.Size = new System.Drawing.Size(649, 206);
                                dgv.TabIndex = 0;
                                BindingSource bindingSource1 = new BindingSource(ds, ds.TableName);
                                dgv.DataSource = bindingSource1;
                                MesajeTB.Text += "Au fost Afectate " + ds.Rows.Count.ToString() + " randuri!\n";
  
                            }
                        }
                    }
                    catch (Exception exc)
                    {
                        string text = "";

                        Exception innerException = exc;
                        while (innerException != null)
                        {
                            text += innerException.Message;
                            text += "\n\n";
                            innerException = innerException.InnerException;
                        }

                        MesajeTB.Text = text;
                        tabControl1.SelectedTab = MesajeTP;
                    }

                }
            }


            
                
           
        }
    }
}
